from fastapi import APIRouter, Depends, HTTPException, status, UploadFile, File
from sqlalchemy.orm import Session
from sqlalchemy import func, or_
from typing import List, Optional
from app.core.database import get_db
from app.core.deps import get_current_admin_user
from app.schemas.customer import CustomerCreate, CustomerUpdate, CustomerResponse, CustomerWithProjects
from app.models.customer import Customer
from app.models.project import Project
from app.models.user import User
from openpyxl import Workbook, load_workbook
from io import BytesIO
from fastapi.responses import StreamingResponse
import re

router = APIRouter()

@router.get("", response_model=List[CustomerWithProjects])
def get_customers(
    skip: int = 0,
    limit: int = 100,
    search: Optional[str] = None,
    project_filter: Optional[str] = None,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_admin_user)
):
    query = db.query(
        Customer,
        func.count(Project.id).label("project_count")
    ).outerjoin(Project)
    
    if search:
        query = query.filter(
            or_(
                Customer.name.contains(search),
                Customer.phone.contains(search)
            )
        )
    
    query = query.group_by(Customer.id)
    
    if project_filter == "0":
        query = query.having(func.count(Project.id) == 0)
    elif project_filter == "1":
        query = query.having(func.count(Project.id) == 1)
    elif project_filter == "multiple":
        query = query.having(func.count(Project.id) > 1)
    
    results = query.offset(skip).limit(limit).all()
    
    customers_with_projects = []
    for customer, project_count in results:
        latest_project = db.query(Project).filter(
            Project.customer_id == customer.id
        ).order_by(Project.updated_at.desc()).first()
        
        customer_dict = CustomerWithProjects(
            id=customer.id,
            user_id=customer.user_id,
            name=customer.name,
            phone=customer.phone,
            member_discount=customer.member_discount,
            notes=customer.notes,
            created_at=customer.created_at,
            updated_at=customer.updated_at,
            project_count=project_count,
            latest_project_status=latest_project.status if latest_project else None
        )
        customers_with_projects.append(customer_dict)
    
    return customers_with_projects

@router.post("", response_model=CustomerResponse)
def create_customer(
    customer: CustomerCreate,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_admin_user)
):
    existing = db.query(Customer).filter(Customer.phone == customer.phone).first()
    if existing:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="该手机号已存在"
        )
    
    db_customer = Customer(**customer.model_dump())
    db.add(db_customer)
    db.commit()
    db.refresh(db_customer)
    return db_customer

@router.get("/{customer_id}", response_model=CustomerResponse)
def get_customer(
    customer_id: int,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_admin_user)
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="客户不存在"
        )
    return customer

@router.put("/{customer_id}", response_model=CustomerResponse)
def update_customer(
    customer_id: int,
    customer_update: CustomerUpdate,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_admin_user)
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="客户不存在"
        )
    
    for key, value in customer_update.model_dump(exclude_unset=True).items():
        setattr(customer, key, value)
    
    db.commit()
    db.refresh(customer)
    return customer

@router.delete("/{customer_id}")
def delete_customer(
    customer_id: int,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_admin_user)
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="客户不存在"
        )
    
    db.delete(customer)
    db.commit()
    return {"message": "删除成功"}

@router.get("/export-template/download")
def download_template(current_user: User = Depends(get_current_admin_user)):
    wb = Workbook()
    ws = wb.active
    ws.title = "客户导入模板"
    
    ws['A1'] = "姓名"
    ws['B1'] = "手机号"
    ws['C1'] = "备注"
    
    ws['A2'] = "张三"
    ws['B2'] = "13800138000"
    ws['C2'] = "示例客户"
    
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    return StreamingResponse(
        output,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=customer_import_template.xlsx"}
    )

@router.post("/import")
async def import_customers(
    file: UploadFile = File(...),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_admin_user)
):
    if not file.filename.endswith(('.xlsx', '.xls')):
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="仅支持Excel文件"
        )
    
    content = await file.read()
    wb = load_workbook(BytesIO(content))
    ws = wb.active
    
    success_count = 0
    fail_count = 0
    errors = []
    
    for row in ws.iter_rows(min_row=2, values_only=True):
        if not row[0] or not row[1]:
            continue
        
        name = str(row[0]).strip()
        phone = str(row[1]).strip()
        notes = str(row[2]).strip() if row[2] else None
        
        if not re.match(r'^1[3-9]\d{9}$', phone):
            fail_count += 1
            errors.append(f"第{ws.iter_rows().__next__()}行: 手机号格式错误")
            continue
        
        existing = db.query(Customer).filter(Customer.phone == phone).first()
        if existing:
            fail_count += 1
            errors.append(f"手机号{phone}已存在")
            continue
        
        customer = Customer(name=name, phone=phone, notes=notes)
        db.add(customer)
        success_count += 1
    
    try:
        db.commit()
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"导入失败: {str(e)}"
        )
    
    return {
        "success_count": success_count,
        "fail_count": fail_count,
        "errors": errors
    }

